/****************************************************************************************
* This program reads in the raw schedule SB files 
****************************************************************************************/

%macro schedsb0910 (yy);

	data blue.schsb_20&yy   ;
       	infile "/.../F5500/SB schedules/F_SCH_SB_20&yy./F_SCH_SB_20&yy._all.csv" delimiter = ',' MISSOVER DSD lrecl=/*redacted*/ firstobs=/*redacted*/ ;
	/*redacted*/
      run;
      
   
	/*Do some re-naming and cleaning*/

	data blue.schsb_20&yy (keep= opr_ein opr_pn filing_id_char SB_CURR_VALUE_AST_01_AMT SB_TOT_FNDNG_TGT_AMT 
	     SB_LIAB_ACT_TOT_FNDNG_TGT_AMT SB_RTD_FNDNG_TGT_AMT 
	     SB_TGT_NRML_COST_01_AMT SB_WEIGHTED_RTM_AGE 
	     SB_ACT_PARTCP_CNT SB_RTD_PARTCP_CNT SB_TERM_PARTCP_CNT plan_year_end
	     rename = (SB_CURR_VALUE_AST_01_AMT = plan_assets
		SB_TOT_FNDNG_TGT_AMT = plan_liab
		SB_LIAB_ACT_TOT_FNDNG_TGT_AMT = actives_liab
		SB_RTD_FNDNG_TGT_AMT = rtd_liab
		SB_TGT_NRML_COST_01_AMT = xpct_liab_incr
		SB_WEIGHTED_RTM_AGE = plan_R_age
		SB_ACT_PARTCP_CNT = actives_cnt
		SB_RTD_PARTCP_CNT = rtd_cnt
		SB_TERM_PARTCP_CNT = term_cnt));
		set blue.schsb_20&yy;
		filing_id = /*redacted*/;			/*numeric id*/
		filing_id_char = /*redacted*/;				/*string filing id*/
		opr_ein = left(trim(sb_ein));				/*rename for consistency with research file*/
		opr_pn = left(trim(sb_pn));					/*rename for consistency with research file*/
		len_pn = length(opr_pn);					/*length of pn field*/
		if len_pn=/*redacted*/ then opr_pn = /*redacted*/;
		else if len_pn=/*redacted*/ then opr_pn = /*redacted*/;
                /*Redacted: removing invalid EIN/PNs*/		  
	   	SB_TAX_PRD = left(trim(SB_TAX_PRD)); 
		ftp_yr = /*redacted*/; 				/*plan end year*/
		ftp_mth = /*redacted*/; 				/*plan end month*/
		ftp_day = /*redacted*/;				/*plan end day*/
		ftp_date = mdy(ftp_mth,ftp_day,ftp_yr);					/*plan end sas date*/
		plan_qtr_end = qtr(ftp_date);						/*plan calendar end qtr*/
		plan_year_end = year(ftp_date);						/*plan calendar end year*/		
	run;

	/*Re-cast some variables from char to numeric*/
	data blue.schsb_20&yy (drop = plan_R_age actives_cnt rtd_cnt term_cnt
	      rename = (actives_cnt_n=actives_cnt rtd_cnt_n=rtd_cnt term_cnt_n=term_cnt plan_R_age_n = plan_R_age));
	      set blue.schsb_20&yy;      
	      plan_R_age_n=/*redacted*/;
	      actives_cnt_n=/*redacted*/;
	      rtd_cnt_n=/*redacted*/;
	      term_cnt_n=/*redacted*/;
	run; 
      
%mend;

%macro schedsb1114 (yy);
	data blue.schsb_20&yy    ;

       %if 20&yy. < 2012 %then %do;
       infile "/.../F5500/SB schedules/F_SCH_SB_20&yy./F_SCH_SB_20&yy._all.csv" delimiter = ',' MISSOVER DSD lrecl=/*redacted*/ firstobs=/*redacted*/ ;
       %end;
       
       %else %do;
        infile "/...F5500/2012_2014_SB_Schedules/F_SCH_SB_20&yy./F_SCH_SB_20&yy._all.csv" delimiter = ',' MISSOVER DSD lrecl=/*redacted*/ firstobs=/*redacted*/ ;
       %end;
	/*redacted*/
      run;
      
     
      /*Do some re-naming and cleaning*/

      data blue.schsb_20&yy (keep= opr_ein opr_pn filing_id_char SB_CURR_VALUE_AST_01_AMT SB_TOT_FNDNG_TGT_AMT 
	     SB_LIAB_ACT_TOT_FNDNG_TGT_AMT SB_RTD_FNDNG_TGT_AMT 
	     SB_TGT_NRML_COST_01_AMT SB_WEIGHTED_RTM_AGE 
	     SB_ACT_PARTCP_CNT SB_RTD_PARTCP_CNT SB_TERM_PARTCP_CNT plan_year_end plan_year_end
	    rename = (SB_CURR_VALUE_AST_01_AMT = plan_assets
		SB_TOT_FNDNG_TGT_AMT = plan_liab
		SB_LIAB_ACT_TOT_FNDNG_TGT_AMT = actives_liab
		SB_RTD_FNDNG_TGT_AMT = rtd_liab
		SB_TGT_NRML_COST_01_AMT = xpct_liab_incr
		SB_WEIGHTED_RTM_AGE = plan_R_age
		SB_ACT_PARTCP_CNT = actives_cnt
		SB_RTD_PARTCP_CNT = rtd_cnt
		SB_TERM_PARTCP_CNT = term_cnt));
	      set blue.schsb_20&yy;
	      filing_id = /*redacted*/;			/*numeric id*/
	      filing_id_char = /*redacted*/;				/*string filing id*/
	      opr_ein = left(trim(sb_ein));				/*rename for consistency with research file*/
	      opr_pn = left(trim(sb_pn));					/*rename for consistency with research file*/
	      len_pn = length(opr_pn);					/*length of pn field*/
	      if len_pn=/*redacted*/ then opr_pn = /*redacted*/;
	      else if len_pn=/*redacted*/ then opr_pn = /*redacted*/;
	      /*Redacted: removing invalid EIN/PNs*/	
	      SB_TAX_PRD = left(trim(SB_TAX_PRD)); 
	      ftp_yr = /*redacted*/; 				/*plan end year*/
	      ftp_mth = /*redacted*/; 				/*plan end month*/
	      ftp_day = /*redacted*/;				/*plan end day*/
	      ftp_date = mdy(ftp_mth,ftp_day,ftp_yr);					/*plan end sas date*/
	      plan_qtr_end = qtr(ftp_date);						/*plan calendar end qtr*/
	      plan_year_end = year(ftp_date);						/*plan calendar end year*/		
      run;

      /*Re-cast some variables from char to numeric*/
      data blue.schsb_20&yy (drop = plan_R_age actives_cnt rtd_cnt term_cnt
	    rename = (actives_cnt_n=actives_cnt rtd_cnt_n=rtd_cnt term_cnt_n=term_cnt plan_R_age_n = plan_R_age));
	    set blue.schsb_20&yy;      
	    plan_R_age_n=/*redacted*/;
	    actives_cnt_n=/*redacted*/;
	    rtd_cnt_n=/*redacted*/;
	    term_cnt_n=/*redacted*/;
      run; 
%mend;
